﻿IF OBJECT_ID('dbo.partner_proc_pivot') IS NOT NULL
	DROP PROCEDURE dbo.partner_proc_pivot;
GO

CREATE PROCEDURE dbo.partner_proc_pivot
@SchemeName AS SYSNAME = N'dbo',
@TargetName as SYSNAME = NULL,
@OnRows AS SYSNAME = NULL,
@OnCols AS SYSNAME = NULL,
@AggFunc AS NVARCHAR(12) = N'COUNT',
@AggCol AS SYSNAME = NULL
AS

DECLARE 
	@target NVARCHAR(200),
	@sql NVARCHAR(MAX),
	@cols NVARCHAR(MAX),
	@newline NVARCHAR(2),
	@msg NVARCHAR(500);
	
SET @target = QUOTENAME(@SchemeName) + N'.' + QUOTENAME(@TargetName);
SET @newline = NCHAR(13) + NCHAR(10);
-- 检查是否缺少输入


-- 只允许已存在的表或视图作为输入对象


-- 检查  onrows, oncols, aggcol列是否存在


-- 检查 aggfunc 是否已知函数


-- 构造列

SET  @sql = 
N'SET @result = '    + @newline +
N' STUFF('			 + @newline +
N'	(SELECT N'','' + ' + 
N'		QUOTENAME(pivotCol) AS [Text()]' + @newline +
N'			FROM  (SELECT DISTINCT ' + QUOTENAME(@OnCols) + N' AS pivotCol' + @newline +
N'					FROM ' + @target + N') AS distinctCols' + @newline +
N'						ORDER BY pivotCol' + @newline +
N'			FOR XML PATH('''')),' + @newline + 
N'	 1, 1, N'''');'

EXEC sp_executesql @stmt = @sql, @param = N'@result AS NVARCHAR(MAX) OUTPUT', @result = @cols OUTPUT;

-- 检查cols 是否存在sql注入


-- 创建pivot查询
SET @sql = 
N'SELECT * ' + @newline + 
N'FROM ' + @newline +
N'	( SELECT ' + @newline +
N'	' + QUOTENAME(@onRows) + N',' + @newline +
N'	' + QUOTENAME(@onCols) + N' AS pivotCol,' + @newline +
N'	' + QUOTENAME(@aggCol) + N' AS aggCol' + @newline +
N'	FROM ' + @target +	@newline + 
N'	) AS pivotInput' + @newline +
N'	PIVOT ' + @newline + 
N'	(' + @AggFunc = N'(aggCol)' + @newline +
N'		FOR pivotCol' + @newline +
N'		IN(' + @cols + N')' + @newline +
N'	) AS pivotOutput;';

EXEC sp_executesql @sql;


END
GO